﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace LogisticsDAL
{
    public class TruckDAL
    {
        /// <summary>
        /// 车队信息管理
        /// </summary>
        /// <returns></returns>
        public static DataTable Trucklist(int pageIndex,int pageSize,out int Recout,string truckName,string Leader)
        {
            string tableName = "TruckTeam";
            string pramary = "TeamID";
            string Filed = "1=1";
            if (truckName != "0")
            {
                Filed = Filed + " and TeamName='" + truckName + "'";
            }
            if (!string.IsNullOrWhiteSpace(Leader))
            {
                Filed = Filed + " and Leader='" + Leader + "'";
            }
            DataTable dt = PROC_Helper.Paging(tableName, pramary, pageIndex, pageSize, Filed,out Recout);
            return dt;
        }


        /// <summary>
        /// 车队查询信息
        /// </summary>
        /// <param name="truckname"></param>
        /// <param name="leader"></param>
        /// <returns></returns>
        public static DataTable Truckread(string truckname,string leader)
        {
            string sql = "SELECT TeamID,[TeamName] ,[Leader],[Remark],[CheckInTime],[IsDelete],[AlertTime] FROM [TruckTeam]";
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();

            if (truckname != "0")
            {
                wherelist.Add("TeamName=@CarTeamName");
                pramlist.Add(new SqlParameter("@CarTeamName", truckname));
            }
            if (!string.IsNullOrWhiteSpace(leader))
            {
                wherelist.Add("Leader=@Leader");
                pramlist.Add(new SqlParameter("@Leader", leader));
            }
            string str = string.Join(" and ", wherelist.ToArray());
            if (wherelist.Count > 0)
            {
                sql = sql + " where " + str;
            }
            DataTable dt = DBHelper.GetTable(sql, pramlist.ToArray());
            return dt;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CarTeamID"></param>
        /// <returns></returns>
        public static int delTruck(int CarTeamID)
        {
            string sql = "p_delTruck";
            SqlParameter[] pram =
            {
                new SqlParameter("@Teamid",CarTeamID)
            };
            return DBHelper.Number(sql, pram);
        }

        public static int UpdatTruck(int TeamID,string Ramke)
        {
            string sql = "p_updateTruck";
            SqlParameter[] pram =
            {
                new SqlParameter("@Teamid",TeamID),
                new SqlParameter("@Remark",Ramke)
            };
            return DBHelper.Number(sql, pram);
        }


        //车辆管理信息
        public static DataTable BindCar(int pageIndex,int pagesize,out int Count, string CarNum, string CarTeamName, string Type, string State)
        {
            string TabelName = "View_TruckSel";
            string pramary = "TruckID";
            string filed = "1=1";
            if (!string.IsNullOrWhiteSpace(CarNum))
            {
                filed = filed + " and Number='" + CarNum+"'";
            }
            if (CarTeamName != "0")
            {
                filed = filed + " and TeamName='" + CarTeamName + "'";
            }
            if (Type != "-1")
            {
                filed = filed + " and Type='" + Type + "'";
            }
            if (State != "-1")
            {
                filed = filed + " and State='" + State + "'";
            }
            
            DataTable dt = PROC_Helper.Paging(TabelName,pramary,pageIndex,pagesize,filed,out Count);
            return dt;
        }


        //三个下拉框绑定
        public DataTable ddlbindCarType()
        {
            string sql = "SELECT distinct([Type]) from [Truck],TruckTeam where Truck.TeamID=TruckTeam.TeamID";
            SqlParameter[] pram =
           {

            };
            DataTable dt = DBHelper.GetTable(sql, pram);
            return dt;
        }


        public DataTable ddlbindCarTeam()
        {
            string sql = "SELECT  [TeamName],TeamID from TruckTeam ";
            SqlParameter[] pram =
           {

            };
            DataTable dt = DBHelper.GetTable(sql, pram);
            return dt;
        }


        public DataTable ddlbindCarState()
        {
            string sql = "SELECT  distinct[State],case State when 1 then '承运中' else '空闲中' end as State from [Truck],TruckTeam where Truck.TeamID=TruckTeam.TeamID";
            SqlParameter[] pram =
           {

            };
            DataTable dt = DBHelper.GetTable(sql, pram);
            return dt;
        }

        /// <summary>
        /// 车辆条件查询
        /// </summary>
        /// <param name="CarNum"></param>
        /// <param name="CarTeamName"></param>
        /// <param name="Type"></param>
        /// <param name="State"></param>
        /// <returns></returns>
        public DataTable sqlreadCar(string CarNum,string CarTeamName,string Type,string State)
        {
            StringBuilder sql =new StringBuilder("SELECT  [TruckID],[Number],[BuyDate],[Type],[Length],[Tonnage],[TeamName],[State] ,[Truck].[CheckInTime],[AlterTime] FROM [Truck] inner join TruckTeam on Truck.TeamID=TruckTeam.TeamID ");
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();
            if (!string.IsNullOrWhiteSpace(CarNum))
            {
                wherelist.Add("Number=@CarNum");
                pramlist.Add(new SqlParameter("@CarNum", CarNum));
            }
            if (CarTeamName != "0")
            {
                wherelist.Add("TeamName=@CarTeamName");
                pramlist.Add(new SqlParameter("@CarTeamName", CarTeamName));
            }
            if (Type != "-1")
            {
                wherelist.Add("Type=@Type");
                pramlist.Add(new SqlParameter("@Type", Type));
            }
            if (State != "-1")
            {
                wherelist.Add("State=@State");
                pramlist.Add(new SqlParameter("@State", State));
            }
            if (wherelist.Count > 0)
            {
                string str = string.Join(" and ", wherelist.ToArray());
                sql.Append(" where "+str);
            }
            DataTable da = DBHelper.GetTable(sql.ToString(), pramlist.ToArray());
            return da;
        }


        /// <summary>
        /// 添加车队
        /// </summary>
        /// <returns></returns>
        public int AddTruckTream(string teaName,string Leadr,string Reamrk)
        {
            string sql = "p_addteam";
            TruckTeam team = new TruckTeam();
            SqlParameter[] pram =
            {
                new SqlParameter("@TeamName",teaName),
                new SqlParameter("@Leader",Leadr),
                new SqlParameter("@Remark",Reamrk)
            };
            int rsult = DBHelper.Number(sql, pram);
            return rsult;
        }

        /// <summary>
        /// 添加车辆
        /// </summary>
        /// <param name="teaName"></param>
        /// <param name="Leadr"></param>
        /// <param name="Reamrk"></param>
        /// <returns></returns>
        public int AddTruck(string Number,string BuyDate,string Types,string Length,string Tonnge, int teamid, string Reamrk)
        {
            string sql = "p_addTruck";
            TruckTeam team = new TruckTeam();
            SqlParameter[] pram =
            {
                new SqlParameter("@Number",Number),
                new SqlParameter("@BuyDate",BuyDate),
                new SqlParameter("@Type",Types),
                new SqlParameter("@Length",Length),
                new SqlParameter("@Tonnage",Tonnge),
                new SqlParameter("@TeamID",teamid),
                new SqlParameter("@Remark",Reamrk)
            };
            int rsult = DBHelper.Number(sql, pram);
            return rsult;
        }


        /// <summary>
        /// 车辆详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Trucks bindtruck(int id)
        {
            string sql = "SELECT  [TruckID],[Number],[BuyDate],[Type],[Length],[Tonnage],[State],TeamName,Truck.TeamID,Truck.[Remark] FROM [Truck] inner join TruckTeam on Truck.TeamID=TruckTeam.TeamID where TruckID=@TruckID";
            SqlParameter[] pram =
            {
                new SqlParameter("@TruckID",id)
            };
            SqlDataReader read = DBHelper.getExreaders(sql, pram);
            Trucks tr = new Trucks();
            if (read.HasRows)
            {
                while (read.Read())
                {
                    
                    tr.Number = read["Number"].ToString();
                    tr.BuyDate = Convert.ToDateTime(read["BuyDate"]);
                    tr.Type = read["Type"].ToString();
                    tr.Length = read["Length"].ToString();
                    tr.Tonnage = read["Tonnage"].ToString();
                    tr.State = Convert.ToInt32(read["State"]);
                    tr.TeamID =Convert.ToInt32(read["TeamID"]);
                    tr.Remark = read["Remark"].ToString();
                    tr.TeamName = read["TeamName"].ToString();
                   
                }
                read.Close();
            }
            return tr;
        }

        /// <summary>
        /// 车辆修改
        /// </summary>
        /// <param name="trk"></param>
        /// <returns></returns>
        public int updaTruck(Trucks trk)
        {
            string sql = "update Truck set Number=@Number,BuyDate=@Buydate,Type=@type,Length=@length,Tonnage=@tonnger,State=@State,TeamID=@TeamID,Remark=@Remark,AlterTime=GetDate() where TruckID=@Truckid";
            SqlParameter[] pram = {
                new SqlParameter("@Number",trk.Number),
                new SqlParameter("@Buydate",trk.BuyDate),
                new SqlParameter("@type",trk.Type),
                new SqlParameter("@length",trk.Length),
                new SqlParameter("@tonnger",trk.Tonnage),
                new SqlParameter("@State",trk.State),
                new SqlParameter("@TeamID",trk.TeamID),
                new SqlParameter("@Remark",trk.Remark),
                new SqlParameter("@Truckid",trk.TruckID)
            };
            int result = DBHelper.Numbers(sql,pram);
            return result;
        }

        /// <summary>
        /// 删除车辆信息
        /// </summary>
        /// <param name="tid"></param>
        /// <returns></returns>
        public int deletTruck(int tid)
        {
            string sql = "  delete Truck where TruckID=@Truid";
            SqlParameter[] pram =
            {
                new SqlParameter("@Truid",tid)
            };
            return DBHelper.Numbers(sql, pram);
        }


        public bool readstruck(int teamID)
        {
            string sql = "select * from Truck where TeamID=@teamID";
            SqlParameter[] pram =
            {
                new SqlParameter("@teamID",teamID)
            };
            SqlDataReader read=DBHelper.getExreaders(sql,pram);
            bool tb = true;
            if (read.HasRows)
            {
                 tb = true;
            }
            else
            {
                 tb = false;
            }
            return tb;
        }



        public List<Trucks> read()
        {
            string sql = "P_TeamTruckBind";
            SqlParameter[] pram ={ };
            SqlDataReader read = DBHelper.Exreader(sql, pram);
            List<Trucks> trs = new List<Trucks>();

            if (read.HasRows)
            {
                while (read.Read())
                {
                    Trucks tu = new Trucks();
                    tu.TeamID = Convert.ToInt32(read["TeamID"]);
                    tu.TeamName = read["TeamName"].ToString();
                    trs.Add(tu);
                }
                read.Close();
            }
            return trs;
        }


        public List<Trucks> readNumber(int TeamID)
        {
            string sql = "P_TeamTruck";
            SqlParameter[] pram = {
                new SqlParameter("@TeamID",TeamID)
            };
            SqlDataReader read = DBHelper.Exreader(sql, pram);
            List<Trucks> trs = new List<Trucks>();

            if (read.HasRows)
            {
                while (read.Read())
                {
                    Trucks tu = new Trucks();
                    tu.Number = read["Number"].ToString();
                    trs.Add(tu);
                }
                read.Close();
            }
            return trs;
        }


        public SqlDataReader truckSelect(int TeamID)
        {
            string sql = " SELECT * FROM dbo.Truck INNER JOIN dbo.TruckTeam ON TruckTeam.TeamID = Truck.TeamID WHERE dbo.TruckTeam.TeamID=@teamid";
            SqlParameter[] pram =
            {
                new SqlParameter("@teamid",TeamID)
            };
            SqlDataReader read = DBHelper.getExreaders(sql, pram);
            return read;

        }
    }

}
